package com.telecom.cms.actions;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.telecom.cms.dao.GetConnect;
import com.telecom.cms.dao.LOC_LATNDAO;
import com.telecom.cms.dao.SEG_BIZ_ATOM_DEFDAO;
import com.telecom.cms.dao.SEG_USER_SQL_LOGDAO;
import com.telecom.cms.dbconnect.Changelatn;
import com.telecom.cms.dbconnect.Chinese;
import com.telecom.cms.dto.LOC_LATNDTO;
import com.telecom.cms.dto.SEG_BIZ_ATOM_DEFDTO;
import com.telecom.cms.dto.ShowatomDTO;
import com.telecom.cms.dto.TOTALDTO;
import com.telecom.cms.util.Constant;
import com.telecom.cms.util.CurrentTime;

/**
 * @author yueyingnishang
 * 
 */
public class DoqueryAction extends Action {
	/*
	 * (non-Javadoc)
	 */
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) throws Exception {
		// TODO Auto-generated method stub
		
		Connection con = GetConnect.getCsConnection();
		SEG_BIZ_ATOM_DEFDAO dao = new SEG_BIZ_ATOM_DEFDAO(con);
		
		List atomlist = (List) request.getSession().getAttribute("AtomList");
		List outputlist = (List) request.getSession().getAttribute("Outputlist");
		if (outputlist == null)
			return mapping.findForward("failure");
		
		String purpose = request.getParameter("purpose");
		String fanye = request.getParameter("fanye");
		if (fanye == null) 
			fanye = (String) request.getAttribute("fanye");
		if (purpose == null && fanye == null)
			return mapping.findForward("failure");
		purpose = Chinese.toChina(purpose);
		if (purpose == "" && fanye == null)
			return mapping.findForward("failure");

		String billing_cycle_y = ((String) request.getSession().getAttribute("billing_cycle_y")).trim();
		request.setAttribute("billing_cycle_y", billing_cycle_y);
		String billing_cycle_m = ((String) request.getSession().getAttribute("billing_cycle_m")).trim();
		request.setAttribute("billing_cycle_m", billing_cycle_m);

		String latnid = ((String) request.getSession().getAttribute(("latnid"))).trim();
		String latncd = ((String) request.getSession().getAttribute(("latncd"))).trim();
		String userid = ((String) request.getSession().getAttribute(("userid")));
		String oldid = ((String) request.getSession().getAttribute(("oldid")));
		if (userid == null)
			userid = "未知";
		else
			userid = Chinese.decode(userid.trim(), "gbk");
		if (latnid.equals("1000")) {
			String latn_id = ((String) request.getSession().getAttribute(("selectlatnid"))).trim();
			LOC_LATNDTO dto = new LOC_LATNDAO().findById(latn_id);
			String latn_cd = dto.getLatn_cd();
			String latn_desc = dto.getLatn_desc();
			latnid = latn_id;
			latncd = latn_cd;
			request.getSession().setAttribute("selectlatndesc", latn_desc);
			request.getSession().setAttribute("selectlatncd", latn_cd);
		}

		String billing_cycle_id = "";
		if (billing_cycle_m.length() == 1) 
			billing_cycle_id = (billing_cycle_y + "0" + billing_cycle_m).trim();
		else
			billing_cycle_id = (billing_cycle_y + billing_cycle_m).trim();
		request.getSession().setAttribute("billing_cycle_id", billing_cycle_id);

		// /加入用户默认字段和来源表
		String rssql = Constant.getStPara("doqueryfirstsql");
		String endsql = "";
		List sqllist = new ArrayList();
		
		Set basTableSet = new TreeSet();
		Set dmnTableSet = new TreeSet();
		// end of codestap1
		for (int i = 0; i < outputlist.size(); i++) { // 处理结果中的金额和时长
			SEG_BIZ_ATOM_DEFDTO dto = (SEG_BIZ_ATOM_DEFDTO) outputlist.get(i);
			sqllist.add(dto);
			if (dto.getSRC_TABLE_COL_NAME() == null || dto.getSRC_TABLE_COL_NAME().trim().equals("") || dto.getSHOW_TYPE_ID() == 1) {
				if (dto.getBIZ_ATOM_FLAG().equalsIgnoreCase("money"))
					rssql += "\t," + dto.getBAS_TABLE_NAME().trim()
							+ "." + dto.getBAS_TABLE_COL_NAME().trim()
							+ "/100 AS " + dto.getBAS_TABLE_COL_NAME().trim() + "\n";
				else if (dto.getBIZ_ATOM_FLAG().equalsIgnoreCase("time"))
					rssql += "\t," + dto.getBAS_TABLE_NAME().trim()
							+ "." + dto.getBAS_TABLE_COL_NAME().trim()
							+ "/60.00 AS " + dto.getBAS_TABLE_COL_NAME().trim() + "\n";
				else
					rssql += "\t," + dto.getBAS_TABLE_NAME().trim()
							+ "." + dto.getBAS_TABLE_COL_NAME().trim() + "\n";
			}
			else {
				rssql += "\t," + dto.getCDE_SRC_TABLE_NAME().trim()
						+ "." + dto.getSRC_TABLE_COL_NAME().trim() + "\n";
			}
			basTableSet.add(dto.getBAS_TABLE_NAME().trim());
			if (dto.getSRC_TABLE_COL_NAME() != null && !dto.getSRC_TABLE_COL_NAME().trim().equals("") && dto.getSHOW_TYPE_ID() != 1)
				dmnTableSet.add(dto.getCDE_SRC_TABLE_NAME().trim() + "#" + dto.getBAS_TABLE_NAME().trim() + "#" + dto.getBAS_TABLE_COL_NAME().trim());
		}

		for (int k = 0; k < atomlist.size(); k++) {
			ShowatomDTO showdto = (ShowatomDTO) atomlist.get(k);
			SEG_BIZ_ATOM_DEFDTO atomdto = dao.findById(showdto.getAtomid());
			basTableSet.add(atomdto.getBAS_TABLE_NAME().trim());
			if ((atomdto.getSHOW_TYPE_ID() == 2 || atomdto.getSHOW_TYPE_ID() == 3) && ((showdto.getAtomvalue() != null) || (showdto.getAtomvalue() != ""))) {
				String values = "";
				if (atomdto.getVALUE_TYPE_ID() == 2 || atomdto.getVALUE_TYPE_ID() == 2000) {
					String[] valuechar = showdto.getAtomvalue().split(",");
					for (int p = 0; p < valuechar.length; p++) {
						if (p == 0)
							values = "'" + valuechar[p] + "'";
						else
							values = values + ", '" + valuechar[p] + "'";
					}
				} 
				else{
					values = showdto.getAtomvalue().substring(0,showdto.getAtomvalue().length() - 1);
				}
				// 加入套餐表过滤条件
				if (atomdto.getBAS_TABLE_COL_NAME().indexOf(Constant.getStPara("promcolname")) > -1 && values.indexOf(Constant.getStPara("nopromid")) > -1)
					endsql += " \n AND " + Constant.getStPara("bastablename") + ".prd_inst_id NOT IN (SELECT Prd_Inst_Id FROM "
							+ atomdto.getBAS_TABLE_NAME().trim() + " WHERE Latn_Id="
							+ latnid + " AND Billing_Cycle_Id="
							+ billing_cycle_id + " AND Prd_Prom_Stat_Id=10) ";
				else if (atomdto.getBAS_TABLE_COL_NAME().indexOf(Constant.getStPara("promcolname")) > -1)
					endsql += " \n AND CAST("
							+ atomdto.getBAS_TABLE_NAME().trim() + "."
							+ atomdto.getBAS_TABLE_COL_NAME().trim()
							+ " AS INTEGER) IN(" + values + ") ";
				else
					endsql += " \n AND " + atomdto.getBAS_TABLE_NAME().trim()
							+ "." + atomdto.getBAS_TABLE_COL_NAME().trim()
							+ " IN (" + values + ") ";
				if (atomdto.getBAS_TABLE_COL_NAME().indexOf(Constant.getStPara("promcolname")) > -1
						&& values.indexOf(Constant.getStPara("nopromid")) < 0)
					endsql += "\n AND " + Constant.getStPara("prombastablename") + ".Prd_Prom_Stat_Id=10";
			} 
			else if (atomdto.getSHOW_TYPE_ID() == 100001 && ((showdto.getAtomvalue() != null) || (showdto.getAtomvalue() != ""))) {//处理时长,金额
				String[] va = showdto.getAtomvalue().split(",");
				String bb = "";
				for (int n = 0; n < va.length; n++) {
					String num[] = va[n].split("－");
					for (int i = 0; i < num.length; i++)
						num[i] = num[i].trim();
					String preFix = n == 0 ? "\n" : "\nOR ";
					if ("money".equalsIgnoreCase(atomdto.getBIZ_ATOM_FLAG().trim()))
						bb += preFix + "(" + atomdto.getBAS_TABLE_NAME().trim() + "."
								+ atomdto.getBAS_TABLE_COL_NAME().trim()
								+ " BETWEEN " + Float.parseFloat(num[0])
								* 100 + " AND " + Float.parseFloat(num[1])
								* 100 + ")";
					else if ("time".equalsIgnoreCase(atomdto.getBIZ_ATOM_FLAG()))
						bb += preFix + "(" + atomdto.getBAS_TABLE_NAME().trim() + "."
								+ atomdto.getBAS_TABLE_COL_NAME().trim()
								+ " \nBETWEEN " + Float.parseFloat(num[0])
								* 60 + " AND " + Float.parseFloat(num[1])
								* 60 + ")";
					else
						bb += preFix + "(" + atomdto.getBAS_TABLE_NAME().trim() + "."
								+ atomdto.getBAS_TABLE_COL_NAME().trim()
								+ " \nBETWEEN " + Float.parseFloat(num[0])
								+ " AND " + Float.parseFloat(num[1]) + ")";
				}
				endsql = endsql + " \n AND " + "(" + bb + ")";
			}
			else if (atomdto.getSHOW_TYPE_ID() == 1 && ((null != showdto.getAtomvalue()) || ("".equalsIgnoreCase(showdto.getAtomvalue())))) {
				String[] cc = showdto.getAtomvalue().split(",");
				String inputvalue = "";
				for (int ii = 0; ii < cc.length; ii++) {
					cc[ii] = "'%" + cc[ii] + "%'";
					String preFix = ii == 0 ? "" : " \nOR ";
					inputvalue = inputvalue + preFix + atomdto.getBAS_TABLE_NAME().trim() 
							+ "." + atomdto.getBAS_TABLE_COL_NAME().trim()
							+ " \nLIKE " + cc[ii];
				}
				endsql = endsql + " \n  AND  " + inputvalue;
			}
			else if (atomdto.getSHOW_TYPE_ID() == 200001 && ((null != showdto.getAtomvalue()) || ("" != showdto.getAtomvalue()))) {
				endsql +=  " \n AND "
						+ atomdto.getBAS_TABLE_NAME().trim()
						+ "." + atomdto.getBAS_TABLE_COL_NAME().trim()
						+ "= '" + showdto.getAtomvalue().substring(0,showdto.getAtomvalue().length() - 1) + "' ";
			} 
			else if (atomdto.getSHOW_TYPE_ID() == 4) {
				endsql +=  " \n AND "
						+ "PV_MART_F.BAS_PRD_INST_CUR."
						+ showdto.getAtomvalue().substring(0,showdto.getAtomvalue().length() - 1);
			}
		}
		
		StringBuffer fromStr = new StringBuffer();
		String basTable = Constant.getStPara("bastablename");
		fromStr.append("FROM\t\t" + basTable + "\n");
		Object[] tables = basTableSet.toArray();
		for (int i = 0; i < tables.length; i++){
			if (tables[i].toString().equalsIgnoreCase(basTable)){
				continue;
			}
			else{
				if (tables[i].toString().equalsIgnoreCase(Constant.getStPara("owebastablename"))){
					fromStr.append("LEFT JOIN\t" + tables[i] + "\n");
					fromStr.append("ON\t\t" + basTable + ".Prd_Inst_Id = " + tables[i] + ".Prd_Inst_Id \n");
					fromStr.append("AND\t\t" + tables[i] + ".Date_Id = " + CurrentTime.getPDate() + " \n");
				}
				else if (Constant.getStPara("NoMonthJoinTable").indexOf(tables[i].toString()) != -1){
					fromStr.append("LEFT JOIN\t" + tables[i] + "\n");
					fromStr.append("ON\t\t" + basTable + ".Prd_Inst_Id = " + tables[i] + ".Prd_Inst_Id \n");
				}
				else{
					fromStr.append("LEFT JOIN\t" + tables[i] + "\n");
					fromStr.append("ON\t\t" + basTable + ".Prd_Inst_Id = " + tables[i] + ".Prd_Inst_Id \n");
					fromStr.append("AND\t\t" + tables[i] + ".Billing_Cycle_Id = '" + billing_cycle_id + "' \n");
				}
				if (tables[i].toString().equalsIgnoreCase(Constant.getStPara("prombastablename")))
					fromStr.append("AND\t\t" + tables[i] + ".Prd_Prom_Stat_Id=10 \n");
			}
		}
		Object[] dmnTables = dmnTableSet.toArray();
		for (int i = 0; i < dmnTables.length; i++){
			String[] dt = dmnTables[i].toString().split("#");
			fromStr.append("LEFT JOIN\t" + dt[0] + "\n");
			fromStr.append("ON\t\t" + dt[1] + "." + dt[2] + " = " + dt[0] + "." + dt[2] + " \n");
		}
		String tabname = fromStr.toString();
		
		String besql = "";
		if (!Constant.getStPara("FixedConditionSql").trim().equals("")){
			if ("".equalsIgnoreCase(endsql))
				besql = rssql + tabname + "WHERE " + Constant.getStPara("FixedConditionSql") + " ";
			else 
				besql = rssql + tabname + "WHERE " + endsql.substring(endsql.indexOf("AND") + 3) + "\n AND " + Constant.getStPara("FixedConditionSql") + " ";
		}
		
		if (Constant.getStPara("BasTableIsCurrent").trim().equalsIgnoreCase("NO")){
			if ("".equalsIgnoreCase(endsql))
				besql = rssql + tabname + "WHERE " + basTable + ".Billing_Cycle_Id = '" + billing_cycle_id + "' \n";
			else 
				besql = rssql + tabname + "WHERE " + endsql.substring(endsql.indexOf("AND") + 3) + "\n AND " + basTable + ".Billing_Cycle_Id = '" + billing_cycle_id + "' \n";
		}
		
		// 确定排序
		String ordersql = " order by ";
		String dordersql = " order by ";
		int ordercount = 0;
		for (int i = 0; i < outputlist.size(); i++) {
			SEG_BIZ_ATOM_DEFDTO dto = (SEG_BIZ_ATOM_DEFDTO) outputlist.get(i);
			if (dto.getORDER_RULE() != null) {
				ordersql = ordersql + (i + 6) + " " + dto.getORDER_RULE() + ",";
				ordercount++;
				if (dto.getORDER_RULE().equals("asc"))
					dordersql = dordersql + (i + 6) + " desc,";
				else
					dordersql = dordersql + (i + 6) + " asc,";
			}
		}
		ordersql = ordersql.trim().substring(0, ordersql.length() - 2);
		dordersql = dordersql.trim().substring(0, dordersql.length() - 2);
		if (ordercount < 1) {
			ordersql = "order by 1 asc";
			dordersql = "order by 1 desc";
		}
		String sql = Changelatn.changeLatnId(latncd, besql);

		System.out.println(sql.toUpperCase());

		int num = sqllist.size() + 5;

		// /取统计信息
		if (Constant.getStPara("RequireTotalData").equalsIgnoreCase("YES")){
			PreparedStatement psnum = null;
			String countsql = null;
			countsql = "select count(*) ,count(distinct Cust_Id) "
					+ ",sum(tanname.Total_Inv_Amt) ,sum(tanname.Total_Inner_Rgn_Amt)     ,sum(tanname.Total_Inter_Rgn_Amt)"
					+ ",sum(tanname.Total_Tol_Amt) ,sum(tanname.Total_Inner_Rgn_Bil_Cnt) ,sum(tanname.Total_Inter_Rgn_Bil_Cnt) "
					+ ",sum(tanname.Total_Tol_Dur) \nfrom  ("
					+ rssql
					+ ",PV_MART_F.BAS_PRD_INST_ALL_SDD_MONTH.Inv_Amt/100       as Total_Inv_Amt"
					+ ",PV_MART_F.BAS_PRD_INST_ALL_SDD_MONTH.Inner_Rgn_Amt/100 as Total_Inner_Rgn_Amt "
					+ ",PV_MART_F.BAS_PRD_INST_ALL_SDD_MONTH.Inter_Rgn_Amt/100 as Total_Inter_Rgn_Amt "
					+ ",PV_MART_F.BAS_PRD_INST_ALL_SDD_MONTH.Tol_Amt/100       as Total_Tol_Amt "
					+ ",PV_MART_F.BAS_PRD_INST_ALL_SDD_MONTH.Inner_Rgn_Bil_Cnt as Total_Inner_Rgn_Bil_Cnt "
					+ ",PV_MART_F.BAS_PRD_INST_ALL_SDD_MONTH.Inter_Rgn_Bil_Cnt as Total_Inter_Rgn_Bil_Cnt "
					+ ",PV_MART_F.BAS_PRD_INST_ALL_SDD_MONTH.Tol_Dur/60        as Total_Tol_Dur "
					+ tabname + "  where "
					+ endsql.substring(endsql.indexOf("AND") + 3) + " and "
					+ basTable
					+ ".Std_Prd_Inst_Stat_Id/100 in (10,11) \n" + " and "
					+ basTable + ".Cde_Src_Table_Id = 1000 "
					+ " and " + basTable
					+ ".Cde_Prd_Inst_Stat_Id = 'F0A' )  tanname";
			countsql = Changelatn.changeLatnId(latncd, countsql);
			psnum = con.prepareStatement(countsql);
			TOTALDTO totalDTO = new TOTALDTO();
			ResultSet rsnew = psnum.executeQuery();
			if (rsnew.next()) {
				totalDTO.setPrd_inst_cnt(rsnew.getInt(1));
				totalDTO.setCust_cnt(rsnew.getInt(2));
				totalDTO.setTotal_Inv_Amt(null == rsnew.getString(3) ? "0" : rsnew.getString(3));
				totalDTO.setTotal_Inner_Rgn_Amt(null == rsnew.getString(4) ? "0" : rsnew.getString(4));
				totalDTO.setTotal_Inter_Rgn_Amt(null == rsnew.getString(5) ? "0" : rsnew.getString(5));
				totalDTO.setTotal_Tol_Amt(null == rsnew.getString(6) ? "0" : rsnew.getString(6));
				totalDTO.setTotal_Inner_Rgn_Bil_Cnt(null == rsnew.getString(7) ? "0" : rsnew.getString(7));
				totalDTO.setTotal_Inter_Rgn_Bil_Cnt(null == rsnew.getString(8) ? "0" : rsnew.getString(8));
				totalDTO.setTotal_Tol_Dur(null == rsnew.getString(9) ? "0" : rsnew.getString(9));
			}
			if (rsnew != null) 
				rsnew.close();
			if (psnum != null)
				psnum.close();
			request.setAttribute("TOTALDTO", totalDTO);
		}
		
		// 处理分页sql
		int nowpage = 1;
		if (request.getParameter("nowpage") != null)
			nowpage = (new Integer(request.getParameter("nowpage"))).intValue();
		int pageSize = 100;
		int idx = 0;
		int startIdx = (nowpage - 1) * pageSize + 1;
		int endIdx = nowpage * pageSize;
		List rslist = new ArrayList();
		
		PreparedStatement psmt = con.prepareStatement(sql + ordersql);
		ResultSet rset = psmt.executeQuery();
		while (rset.next()) {
			idx++;
			if (idx < startIdx)
				continue;
			if (idx > endIdx)
				continue;
			String[] desc = new String[num];
			desc[0] = rset.getString(1);
			desc[1] = rset.getString(2);
			desc[2] = rset.getString(3);
			desc[3] = rset.getString(4);
			desc[4] = rset.getString(5);
			for (int cn = 0; cn < sqllist.size(); cn++) {
				if (rset.getObject(cn + 6) == null) {
					desc[cn + 5] = "0";
				} else {
					desc[cn + 5] = rset.getObject(cn + 6).toString();
				}
			}
			rslist.add(desc);
		}
		if (rset != null) {
			rset.close();
		}
		if (psmt != null) {
			psmt.close();
		}
		
		SEG_USER_SQL_LOGDAO log = new SEG_USER_SQL_LOGDAO(con);
		if (fanye == null) {
			log.insert(userid, oldid, purpose, sql);
		}
		request.setAttribute("LATNID", latnid);
		request.setAttribute("RSLIST", rslist);
		request.getSession().setAttribute("Outputlist", outputlist);
		request.getSession().setAttribute("Sqllist", sqllist);
		request.setAttribute("RSCOUNTNUM", idx);
		request.getSession().setAttribute("SQL", sql);
		request.setAttribute("nowpage", new Integer(nowpage));
		return mapping.findForward("success");
	}
}